package dal.project;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;




import java.util.Collections;

import dal.database.DBUtil;
import dal.database.DataInterface;
import dal.employee.Employee;

public class ProjectUtil implements DataInterface {

/*	+------------+-------------+------+-----+---------+-------+
	| Field      | Type        | Null | Key | Default | Extra |
	+------------+-------------+------+-----+---------+-------+
	| Project_id | varchar(30) | NO   | PRI |         |       |
	| Date       | date        | YES  |     | NULL    |       |
	| Title      | tinytext    | YES  |     | NULL    |       |
	| Owner      | tinytext    | YES  |     | NULL    |       |
	| Location   | tinytext    | YES  |     | NULL    |       |
	+------------+-------------+------+-----+---------+-------+
	*/
	/*
	@Override
	public String getId() {
		String id="";
		String qry="select project_id from project";
		ResultSet rs=null;
		try {
			
			rs=DBUtil.getCon().createStatement().executeQuery(qry);
			if(rs.next()){
				rs.last();
				
				String tempId=rs.getString(1);
				
				//System.out.println("tempId  = "+tempId);
				
				String intId=tempId.substring(3);
				
				//System.out.println("intId  = "+intId);
				int firstOccurenceOfNonZero=0;
				int i=0;
				while(i<intId.length()){
					
					char c=intId.charAt(i);
					if(c=='0'){
						firstOccurenceOfNonZero++;
					}else{
						//firstOccurenceOfNonZero++;
						break;
					}
					i++;
				}
				
				//System.out.println("ocZero ="+firstOccurenceOfNonZero );
				
				Integer intPart=Integer.parseInt(intId);
				intPart++;
				
				String finalId="";
				i=0;
				while(i<firstOccurenceOfNonZero){
					finalId=finalId+"0";
					
					i++;
				}
				
				//System.out.println("final = "+finalId);
				
				id="PRJ"+finalId+intPart;
				
				//System.out.println("id = "+id);
				
			}else{
				
				id="PRJ00001";
			}
			
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return id;
	}*/

	
	@Override
	public String getId() {
		String id="";
		String qry="select project_id from project";
		ResultSet rs=null;
		ArrayList<Integer> al=new ArrayList<Integer>();
		int idIntLength=5;
		try {
			
			rs=DBUtil.getCon().createStatement().executeQuery(qry);
			if(rs.next()){
				
				String tempId=rs.getString(1);
				String strIntId=tempId.substring(3, (tempId.length()));
				//System.out.println("strIntId = "+strIntId);
				
				al.add(Integer.parseInt(strIntId));
				while(rs.next()){
					tempId=rs.getString(1);
					strIntId=tempId.substring(3, (tempId.length()));
					al.add(Integer.parseInt(strIntId));
				}
				
				Collections.sort(al);
				
				//System.out.println("size  = "+al.size());
				
				Integer intId=al.get((al.size()-1));
				intId++;
				strIntId=intId.toString();
				//System.out.println("strIntId = "+strIntId);
				int fillerLength=idIntLength-(strIntId.length());
				//System.out.println("filler lnght  =  "+fillerLength);
				
				int i=0;
				String zeroString="";
				while(i<fillerLength){
					zeroString=zeroString+"0";
					i++;
				}
				
				id="PRJ"+zeroString+strIntId;
				
			}else{
				
				id="PRJ00001";
			}
			
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return id;
	}
	
	@Override
	public boolean add(Object instance) {
		boolean isOk=false;
		
		Project project=(Project)instance;
		String qry="insert into project values(?,?,?,?,?)";
		//System.out.println("Qry  =  "+qry);
		try {
			
			PreparedStatement preparedStatement=DBUtil.getCon().prepareStatement(qry);
			preparedStatement.setString(1,project.getProjectId());
			preparedStatement.setDate(2, project.getDate());
			preparedStatement.setString(3, project.getTitle());
			preparedStatement.setString(4, project.getOwner());
			preparedStatement.setString(5, project.getLocation());
			
			int res=preparedStatement.executeUpdate();
			if(res>0){
				isOk=true;
			}else {
				isOk=false;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}	
		
		return isOk;
	}

	@Override
	public boolean update(Object instance) {
		boolean isOk=false;
		Project project=(Project)instance;
		String qry="update project set Date=?, Title=?, Owner=?, Location=?  where Project_id=?";
		
		try {
			
			PreparedStatement ps=DBUtil.getCon().prepareStatement(qry);
			
			ps.setDate(1,project.getDate());
			ps.setString(2, project.getTitle());
			ps.setString(3, project.getOwner());
			ps.setString(4, project.getLocation());
			ps.setString(5, project.getProjectId());
			
			int res=ps.executeUpdate();
			if(res>0){
				isOk=true;
			}else {
				isOk=false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return isOk;
	}

	@Override
	public Object get(String id) {
		Project prj=null;
		ResultSet rs=null;
		String qry="select * from project where Project_id='"+id+"'";
		
		try {
			rs=DBUtil.getCon().createStatement().executeQuery(qry);
			if(rs.next()){
				prj=new Project();
				prj.setProjectId(rs.getString(1));
				prj.setDate(rs.getDate(2));
				prj.setTitle(rs.getString(3));
				prj.setOwner(rs.getString(4));
				prj.setLocation(rs.getString(5));
			}
			rs.close();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			
		}
		return prj;
	}

	@Override
	public ArrayList<Object> get() {
		ArrayList<Object> al=new ArrayList<Object>();

		ResultSet rs=null;
		String qry="select * from project";
		
		try {
			rs=DBUtil.getCon().createStatement().executeQuery(qry);
			while(rs.next()){
				Project prj=new Project();
				prj.setProjectId(rs.getString(1));
				prj.setDate(rs.getDate(2));
				prj.setTitle(rs.getString(3));
				prj.setOwner(rs.getString(4));
				prj.setLocation(rs.getString(5));
				
				al.add(prj);
			}
			
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
			try {
				if(rs!=null){
					rs.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			
		}
		
		return al;
	}

	@Override
	public boolean delete(String id) {
		// TODO Auto-generated method stub
		return false;
	}

	
	
}
